<?php
defined ( 'IN_SYS' ) or die ( 'Restricted Access' );
require_once SYS_CONFIG.'config.db.php';
require_once SYS_LIB_KERNEL.'sys.logs.php';
class SysDatabases {
	public $config = array();
	public $dbObj;
	public $recordset;
	public $result;
	public $sql;
	public $insert_ids = array();
	public $insert_id = NULL;
	
	public function __construct() {
		$this->config = ConfigDb::$config;
		switch ($this->config['type']) {
			case 'MsSQL':
				require_once SYS_LIB_KERNEL.'dataaccess'.DS.'sys.mssql.php';
				$this->dbObj = new SysMsSql ();
				$this->dbObj->config = $this->config;
				break;
			case 'MySQL':
				require_once SYS_LIB_KERNEL.'dataaccess'.DS.'sys.mysql.php';
				$this->dbObj = new SysMySql ();
				$this->dbObj->config = $this->config;
				break;
			case 'MySQLi':
				require_once SYS_LIB_KERNEL.'dataaccess'.DS.'sys.mysqli.php';
				$this->dbObj = new SysMySqli ();
				$this->dbObj->config = $this->config;
				break;
			case 'Oracle':
				require_once SYS_LIB_KERNEL.'dataaccess'.DS.'sys.oracle.php';
				$this->dbObj = new SysOracle ();
				$this->dbObj->config = $this->config;
				break;
			case 'postgres':
			case 'PgSQL':
				require_once SYS_LIB_KERNEL.'dataaccess'.DS.'sys.postgresql.php';
				$this->dbObj = new SysPostgreSql ();
				$this->dbObj->config = $this->config;
				break;
		
			default:
				throw new SysExceptionModel('System do not support for database type ' . $this->config['type']);
				break;
		}
	}
	
	public function &conn() {
		return $this->dbObj->getConn();
	}
	
	public function getSql() {
		return $this->dbObj->sql;
	}
	
	
	/**
	 * @desc use after run query
	 * @param string $table table name
	 * @param string $fields cols name
	 * @param string $where WHERE
	 * @param string $order = col1 ASC, col2 DESC
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function fetch($table='', $fields='*', $where = null, $order = null, $page_record=-1, $page_no='') {
		$sql = $this->buildSelect($table, $fields, $where, $order);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->fetch($sql);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	/**
	 * @desc use after run query
	 * @param string $table table name
	 * @param string $fields cols name
	 * @param string $where WHERE
	 * @param string $order = col1 ASC, col2 DESC
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function fetchAll($table='', $fields='*', $where = null, $order = null, $page_record=-1, $page_no='') {
		$sql = $this->buildSelect($table, $fields, $where, $order);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->fetchAll($sql);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	/**
	 * @desc get one first of rows
	 * @return array(col1=>val, col2=>val, ...)
	 */
	public function selectOne($table='', $fields='*', $where='', $order = null) {
		$sql = $this->buildSelect($table, $fields, $where, $order);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->selectOne($sql);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	/**
	 * @desc get rows of table
	 * 
	 * @param string $table table name
	 * @param string $fields cols name
	 * @param string $where WHERE
	 * @param string $order = col1 ASC, col2 DESC
	 * @param string $key_name key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * 
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function select($table='', $fields='*', $where = null, $order = null, $key_name = '', $page_record=-1, $page_no='') {
		$sql = $this->buildSelect($table, $fields, $where, $order);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->select($sql,$key_name,$page_record,$page_no);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	/**
	 * @desc insert one row
	 * @param $table
	 * @param $data = array(col1=> val1, col2=>val2, ...);
	 * @param string $key_name
	 * 
	 * @return id insert
	 */
	public function insert($table='', $data=array(), $key_name='') {
		$sql = $this->buildInsert($table, $data);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->insert_id = $this->dbObj->insert($sql, $key_name);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->insert_id;
	}
	
	/**
	 * insert multi row
	 * 
	 * @return ids insert
	 */
	public function insertMulti($table='', $datas=array(), $key_name='') {
		if (!is_array($datas)) {
			throw new SysExceptionModel('Do not data update!');
		}
		
		foreach ($datas as $data) {
			$this->insert_ids[] = $this->insert($table, $data, $key_name);
		}
		return $this->insert_ids;
	}
	
	/**
	 * @desc update value
	 * @param $table
	 * @param $data = array(col1=> val1, col2=>val2, ...);
	 * @param $where
	 * 
	 * @return true|false
	 */
	public function update($table='', $data=array(), $where='', $limit = null) {
		$sql = $this->buildUpdate($table, $data, $where);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->update($sql, $limit);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	
	/**
	 * delete record
	 * @return true|false
	 */
	public function delete($table='', $where='', $limit = null) {
		$sql = $this->buildDelete($table, $where, $limit);
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->result = $this->dbObj->delete($sql, $limit);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->result;
	}
	
	/**
	 * @desc run SQL query
	 * @return recordset
	 */
	public function query($sql=''){
		Page::push('SYS_SQL', $sql);
		SysLogs::pushLogSql($sql);
		
		$this->recordset = $this->dbObj->query($sql);
		
		// end log sql
		SysLogs::endLogSql($this->getSql());
		return $this->recordset;
	}
	
	/**
	 * @desc run after query($sql)
	 * @return as fetch()
	 */
	public function getFetch($recordset){
		return $this->dbObj->getFetch($recordset);
	}
	
	/**
	 * @desc run after query($sql)
	 * @return as fetchAll()
	 */
	public function getFetchAll($recordset){
		return $this->dbObj->getFetchAll($recordset);
	}
	
	/**
	 * @desc run after query($sql)
	 * @return as getOne()
	 */
	public function getOne($recordset){
		$data = $this->dbObj->getFetchAll($recordset);
		if ($data) return $data[0];
		return array();
	}
	
	/**
	 * @desc run after query($sql)
	 * @return as selectOne()
	 */
	public function getSelectOne($recordset){
		return $this->dbObj->getSelectOne($recordset);
	}
	
	/**
	 * @desc run after query($sql)
	 * @return as select()
	 */
	public function getSelect($recordset,$key=''){
		return $this->dbObj->getSelect($recordset,$key);
	}
	
	
	/**
	 * @desc get id after run insert
	 * @return last id of table
	 */
	public function getLastId() {
		return $this->dbObj->getLastId();
	}
	
	/**
	 * @desc get number after run select
	 * @return number of rows
	 */
	public function getNumRows($recordset=NULL) {
		return $this->dbObj->getNumRows($recordset);
	}
	
	/**
	 * @desc alias addslashes
	 */
	public function addslashes($val='') {
		return addslashes($val);
	}
	
	/**
	 * @desc alias close
	 */
	public function close() {
		if (method_exists( $this->dbObj, 'close')) {
			return $this->dbObj->close();
		}
	}
	/**
	 * @return SQL query
	 */
	protected function buildSelect ($table='', $fields='*', $where = null, $order = null) {
		$sql = 'SELECT';
		if ($fields == '') $fields='*';
		$sql.= ' '.$fields;
		$sql.= "\n FROM {$table} ";
		if ($where != '') $sql.= "\n WHERE {$where} ";
		if ($order != '') $sql.= "\n ORDER BY {$order} ";
		
		return $sql;
	}
	/**
	 * @return SQL query
	 */
	protected function buildUpdate ($table='', $data=array(), $where='') {
		if (!is_array($data)) {
			throw new SysExceptionModel('Do not data update!');
		}
		
		$tmp = '';
		$reg = "/\{\[(.*)\]\}/";
		foreach ($data  as $col => $val) {
			preg_match_all ( $reg, '{'.$val.'}', $r );
			if (isset($r[1][0])) {
				$tmp.= $this->addslashes($col). "= " . $this->addslashes($r[1][0]) . ",";
			} else {
				$tmp.= $this->addslashes($col). "= '" . $this->addslashes($val) . "',";
			}
		}
		$tmp = substr($tmp, 0, -1);
		$table = $this->addslashes($table);
		
		$sql = "UPDATE {$table}\n SET {$tmp} ";
		if ($where != '') $sql.= "\n WHERE {$where} ";
		return $sql;
	}
	/**
	 * @return SQL query
	 */
	protected function buildInsert ($table='', $data=array()) {
		if (!is_array($data)) {
			throw new SysExceptionModel('Do not data update!');
		}
		$tmp_col = '';
		$tmp_val = '';
		$reg = "/\{\[(.*)\]\}/";
		foreach ($data  as $col => $val) {
			$tmp_col.= $this->addslashes($col). "," ;
			preg_match_all ( $reg, $val, $r );
			if (isset($r[1][0])) {
				$tmp_val.= $this->addslashes($r[1][0]) . ",";
			} else {
				$tmp_val.= "'" . $this->addslashes($val) . "',";
			}
		}
		$tmp_col = substr($tmp_col, 0, -1);
		$tmp_val = substr($tmp_val, 0, -1);
		$table = $this->addslashes($table);
		
		$sql = "INSERT INTO {$table} ({$tmp_col}) ";
		$sql.= "\n VALUES ({$tmp_val})";
		return $sql;
	}
	/**
	 * @return SQL query
	 */
	protected function buildDelete ($table='', $where='', $limit=NULL) {
		$table = $this->addslashes($table);
		$sql = "DELETE FROM {$table} ";
		if ($where != '') $sql.= "\n WHERE {$where}";
		return $sql;
	}
	
	/**
	 * @desc close connection
	 */
	public function __destruct () {
		if (method_exists( $this->dbObj, '__destruct')) {
			return $this->dbObj->__destruct();
		}
	}
}